# -*- coding:utf-8 -*-
"""
说明：对mysql数据库的操作方法
"""
import os

import pymysql

from AutoTestPlant_AfterEnd.settings import DATABASES
from tools.upl_log import log

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

# print(DATABASES)


class MysqlConnect(object):

    def __init__(self):  # db_name: 对应数据库配置项名
        self.db = pymysql.connect(
            host=DATABASES["default"]["HOST"],
            port=int(DATABASES["default"]["PORT"]),
            user=DATABASES["default"]["USER"],
            passwd=DATABASES["default"]["PASSWORD"],
            db=DATABASES["default"]["NAME"],
            charset='utf8'
        )
        self.cr = self.db.cursor()

    @staticmethod
    def sql_read(file_path):
        """
        功能：读取sql查询语句 （单个文件中存储1条sql语句）
        file_path：sql语句存储路径
        返回值：sql语句
        """
        with open(file_path, 'r', encoding='utf8') as fp:
            tmp = []
            for i in fp:
                tmp.append(i)
            str_sql = [str(i) for i in tmp]
            sql = ''.join(str_sql)
        return sql

    def sql_select(self, sql):
        """
        功能：查询数据库数据
        sql：数据库命令
        返回值：包含以元组形式返回的查询结果的列表
        """
        self.cr.execute(sql)
        rs = self.cr.fetchall()
        return rs

    def sql_select_dict(self, sql):
        """
        功能：查询数据库数据
        sql：数据库命令
        返回值：包含以字典形式返回的查询结果的列表
        """
        rs = self.sql_select(sql)
        cols = [d[0] for d in self.cr.description]
        rs_format = []
        for row in rs:
            rs_dict = dict(zip(cols, row))
            rs_format.append(rs_dict)
        return rs_format

    def sql_iud(self, sql):
        """
        功能：添加、删除、修改数据库记录
        sql：数据库命令
        返回值：None
        """
        try:
            self.cr.execute(sql)
            self.db.commit()
        except pymysql.IntegrityError as ErrInfo:
            log.info("执行错误：{}".format(sql))
            raise ErrInfo

    def clear_db_data(self, path, info):
        """
        功能：清理数据库
        path：清理数据库SQL文件路径
        info：删除条件，用于传入sql
        返回值：None
        """
        with open(path, "r", encoding="utf-8") as f:
            for i in f.readlines():
                self.sql_iud(i.format(parameter=info))

    def get_table_columns(self, sql):
        """
        功能：获取数据库表的所有字段名称
        sql：数据库命令
        返回值：包含所有字段名称的列表
        """
        self.sql_select(sql)
        columns = [d[0] for d in self.cr.description]
        return columns

    def __del__(self):
        self.cr.close()
        self.db.close()


if __name__ == '__main__':
    # data = MysqlConnect().sql_select_dict("select * from auth_app_menu")
    user_list = MysqlConnect().sql_select_dict("select * from auth_app_userinfo")

    # print(user_list)